package com.microframework.base.core.util.sql;

import java.util.*;
import java.util.regex.*;
import com.jfinal.kit.StrKit;

/**
 * SQL 解析工具类（支持字段信息提取和表关联分析）
 */
public class SqlParserUtil {
    private static final Pattern SELECT_PATTERN = Pattern.compile(
        "^SELECT\\s+(.*?)\\s+FROM\\s+", 
        Pattern.CASE_INSENSITIVE | Pattern.DOTALL
    );
    
    private static final Pattern WHERE_PATTERN = Pattern.compile(
        "\\bWHERE\\b(.*?)(\\bGROUP BY\\b|\\bORDER BY\\b|\\bLIMIT\\b|$)", 
        Pattern.CASE_INSENSITIVE | Pattern.DOTALL
    );
    
    private static final Pattern TABLE_ALIAS_PATTERN = Pattern.compile(
        "FROM\\s+([\\w_]+)\\s+(?:AS\\s+)?(\\w+)?", 
        Pattern.CASE_INSENSITIVE
    );
    
    private static final Pattern JOIN_PATTERN = Pattern.compile(
        "JOIN\\s+([\\w_]+)\\s+(?:AS\\s+)?(\\w+)?\\s+ON", 
        Pattern.CASE_INSENSITIVE
    );
    
    /**
     * 获取 SQL 语句中 WHERE 子句之前的部分
     */
    public static String getSqlBeforeWhere(String sql) {
        if (StrKit.isBlank(sql)) {
            return sql;
        }

        String cleanedSql = cleanSql(sql);
        Pattern pattern = Pattern.compile("^(.*?)\\bwhere\\b", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        Matcher matcher = pattern.matcher(cleanedSql);
        
        return matcher.find() ? matcher.group(1).trim() : cleanedSql;
    }

    /**
     * 解析SQL字段信息（包含所属表信息）
     */
//    public static List<FieldInfo> parseFields(String sql, DataSource dataSource) throws SQLException {
//        if (!isSelectStatement(sql)) {
//            throw new IllegalArgumentException("仅支持SELECT语句解析");
//        }
//
//        // 解析表信息（主表和关联表）
//        Map<String, TableInfo> tables = parseTables(sql);
//        List<FieldWithTable> selectFields = extractSelectFields(sql);
//        List<String> whereFields = extractWhereFields(sql);
//
//        return getFieldMetadata(tables, selectFields, whereFields, dataSource);
//    }

    /**
     * 解析SQL中的表信息
     */
    public static Map<String, TableInfo> parseTables(String sql) {
        Map<String, TableInfo> tables = new LinkedHashMap<>();
        String cleaned = cleanSql(sql);
        
        // 解析主表
        Matcher mainTableMatcher = TABLE_ALIAS_PATTERN.matcher(cleaned);
        if (mainTableMatcher.find()) {
            String tableName = mainTableMatcher.group(1);
            String alias = mainTableMatcher.group(2);
            tables.put(alias != null ? alias : tableName, 
                       new TableInfo(tableName, alias, true));
        }
        
        // 解析JOIN表
        Matcher joinMatcher = JOIN_PATTERN.matcher(cleaned);
        while (joinMatcher.find()) {
            String tableName = joinMatcher.group(1);
            String alias = joinMatcher.group(2);
            String key = alias != null ? alias : tableName;
            tables.put(key, new TableInfo(tableName, alias, false));
        }
        
        return tables;
    }

    /**
     * 提取SELECT字段列表（带表信息）
     */
    public static List<FieldWithTable> extractSelectFields(String sql) {
        List<FieldWithTable> result = new ArrayList<>();
        Matcher matcher = SELECT_PATTERN.matcher(cleanSql(sql));
        if (!matcher.find()) {
            return result;
        }

        String fieldsPart = matcher.group(1).trim();
        String[] fields = fieldsPart.split("\\s*,\\s*");
        
        // 匹配字段格式：表别名.字段名 或 字段名
        Pattern fieldPattern = Pattern.compile(
            "(?:([a-zA-Z_][\\w]*)\\.)?([a-zA-Z_][\\w]*)(?:\\s+AS\\s+([\\w]+))?",
            Pattern.CASE_INSENSITIVE
        );
        
        for (String field : fields) {
            Matcher fieldMatcher = fieldPattern.matcher(field);
            if (fieldMatcher.find()) {
                String tableAlias = fieldMatcher.group(1);
                String fieldName = fieldMatcher.group(2);
                String fieldAlias = fieldMatcher.group(3);
                result.add(new FieldWithTable(tableAlias, fieldName, fieldAlias, field));
            }
        }
        
        return result;
    }

    /**
     * 提取WHERE条件中的字段
     */
    public static List<String> extractWhereFields(String sql) {
        List<String> fields = new ArrayList<>();
        Matcher matcher = WHERE_PATTERN.matcher(cleanSql(sql));
        if (!matcher.find()) {
            return fields;
        }

        String whereClause = matcher.group(1).trim();
        Pattern fieldPattern = Pattern.compile("\\b([a-z_][a-z0-9_]*)\\b(?=\\s*[=<>])", Pattern.CASE_INSENSITIVE);
        Matcher fieldMatcher = fieldPattern.matcher(whereClause);
        
        while (fieldMatcher.find()) {
            fields.add(fieldMatcher.group(1));
        }
        
        return fields;
    }

    /**
     * 从数据库获取字段元数据（包含表信息）
     */
//    private static List<FieldInfo> getFieldMetadata(
//        Map<String, TableInfo> tables,
//        List<FieldWithTable> selectFields,
//        List<String> whereFields,
//        DataSource dataSource
//    ) throws SQLException {
//        List<FieldInfo> result = new ArrayList<>();
//        
//        try (Connection conn = dataSource.getConnection()) {
//            DatabaseMetaData meta = conn.getMetaData();
//            
//            // 处理SELECT字段
//            for (FieldWithTable field : selectFields) {
//                // 确定字段所属的表
//                TableInfo tableInfo = resolveTableForField(field, tables);
//                if (tableInfo == null) continue;
//                
//                FieldInfo info = getFieldMetadata(meta, tableInfo.getTableName(), field.getFieldName());
//                if (info != null) {
//                    info.setTableAlias(field.getTableAlias());
//                    info.setTableName(tableInfo.getTableName());
//                    info.setInSelect(true);
//                    info.setAlias(field.getFieldAlias());
//                    result.add(info);
//                }
//            }
//            
//            // 处理WHERE字段
//            for (String field : whereFields) {
//                // 尝试找到包含此字段的表
//                for (TableInfo tableInfo : tables.values()) {
//                    FieldInfo info = getFieldMetadata(meta, tableInfo.getTableName(), field);
//                    if (info != null) {
//                        info.setInWhere(true);
//                        info.setTableName(tableInfo.getTableName());
//                        result.add(info);
//                        break;
//                    }
//                }
//            }
//        }
//        
//        return result;
//    }

    /**
     * 确定字段所属的表
     */
//    private static TableInfo resolveTableForField(FieldWithTable field, Map<String, TableInfo> tables) {
//        // 如果字段指定了表别名
//        if (field.getTableAlias() != null) {
//            return tables.get(field.getTableAlias());
//        }
//        
//        // 未指定表别名时，尝试查找包含该字段的表
//        for (TableInfo table : tables.values()) {
//            try {
//                if (hasField(table.getTableName(), field.getFieldName())) {
//                    return table;
//                }
//            } catch (SQLException e) {
//                // 忽略元数据查询异常
//            }
//        }
//        
//        return null;
//    }

    /**
     * 检查表是否包含字段
     */
//    private static boolean hasField(String tableName, String fieldName) throws SQLException {
////        try (Connection conn = Db.getConfig().getConnection();
////             ResultSet rs = conn.getMetaData().getColumns(null, null, tableName, null)) {
////            while (rs.next()) {
////                if (fieldName.equalsIgnoreCase(rs.getString("COLUMN_NAME"))) {
////                    return true;
////                }
////            }
////        }
//        return false;
//    }

    /**
     * 获取字段元数据
     */
//    private static FieldInfo getFieldMetadata(DatabaseMetaData meta, String tableName, String fieldName) 
//        throws SQLException {
//        try (ResultSet rs = meta.getColumns(null, null, tableName, null)) {
//            while (rs.next()) {
//                String columnName = rs.getString("COLUMN_NAME");
//                if (fieldName.equalsIgnoreCase(columnName)) {
//                    FieldInfo info = new FieldInfo();
//                    info.setName(columnName);
//                    info.setType(rs.getString("TYPE_NAME"));
//                    info.setSize(rs.getInt("COLUMN_SIZE"));
//                    info.setNullable(rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
//                    info.setTableName(tableName);
//                    return info;
//                }
//            }
//        }
//        return null;
//    }

    /**
     * 清理SQL字符串
     */
    private static String cleanSql(String sql) {
        return sql.replaceAll("/\\*.*?\\*/", "")
                  .replaceAll("--.*", "")
                  .replaceAll("\\s+", " ")
                  .trim();
    }

    /**
     * 判断是否为SELECT语句
     */
    public static boolean isSelectStatement(String sql) {
        return cleanSql(sql).toLowerCase().startsWith("select");
    }

    /**
     * 字段信息封装类
     */
    public static class FieldInfo {
        private String tableName;
        private String tableAlias;
        private String name;
        private String alias;
        private String type;
        private int size;
        private boolean nullable;
        private boolean inSelect;
        private boolean inWhere;

        // getters/setters
        public String getTableName() { return tableName; }
        public void setTableName(String tableName) { this.tableName = tableName; }
        public String getTableAlias() { return tableAlias; }
        public void setTableAlias(String tableAlias) { this.tableAlias = tableAlias; }
        public String getName() { return name; }
        public void setName(String name) { this.name = name; }
        public String getAlias() { return alias; }
        public void setAlias(String alias) { this.alias = alias; }
        public String getType() { return type; }
        public void setType(String type) { this.type = type; }
        public int getSize() { return size; }
        public void setSize(int size) { this.size = size; }
        public boolean isNullable() { return nullable; }
        public void setNullable(boolean nullable) { this.nullable = nullable; }
        public boolean isInSelect() { return inSelect; }
        public void setInSelect(boolean inSelect) { this.inSelect = inSelect; }
        public boolean isInWhere() { return inWhere; }
        public void setInWhere(boolean inWhere) { this.inWhere = inWhere; }

        @Override
        public String toString() {
            return String.format(
                "%s.%s%s [%s(%d)] %s %s",
                tableAlias != null ? tableAlias : tableName,
                name,
                alias != null ? "(" + alias + ")" : "",
                type, size,
                nullable ? "NULL" : "NOT NULL",
                (inSelect ? "SELECT" : "") + (inWhere ? " WHERE" : "")
            );
        }
    }

    /**
     * 表信息封装类
     */
    public static class TableInfo {
        private final String tableName;
        private final String alias;
        private final boolean isMainTable;

        public TableInfo(String tableName, String alias, boolean isMainTable) {
            this.tableName = tableName;
            this.alias = alias;
            this.isMainTable = isMainTable;
        }

        public String getTableName() { return tableName; }
        public String getAlias() { return alias; }
        public boolean isMainTable() { return isMainTable; }
        
        @Override
        public String toString() {
            return tableName + (alias != null ? " AS " + alias : "") + 
                   (isMainTable ? " (MAIN)" : " (JOIN)");
        }
    }

    /**
     * 带表信息的字段封装类
     */
    public static class FieldWithTable {
        private final String tableAlias;
        private final String fieldName;
        private final String fieldAlias;
        private final String rawExpression;

        public FieldWithTable(String tableAlias, String fieldName, String fieldAlias, String rawExpression) {
            this.tableAlias = tableAlias;
            this.fieldName = fieldName;
            this.fieldAlias = fieldAlias;
            this.rawExpression = rawExpression;
        }

        public String getTableAlias() { return tableAlias; }
        public String getFieldName() { return fieldName; }
        public String getFieldAlias() { return fieldAlias; }
        public String getRawExpression() { return rawExpression; }
    }
}